package org.tof.hotel.dao;

import org.tof.hotel.dto.AdministratorDTO;
import org.tof.hotel.jdbc.JConnection;
import org.tof.hotel.jdbc.Pool;

import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Class which provides CRUD access to the DB for Administrator objects.
 * @see org.tof.hotel.entity.Administrator
 */
public class AdministratorDaoImpl {
    private Pool pool;

    /**
     * Sole constructor of <code>AdministratorDaoImpl</code>.
     */
    public AdministratorDaoImpl() throws IOException, ClassNotFoundException {
        this.pool = new Pool();
    }

    /**
     * Class constructor of <code>AdministratorDaoImpl</code> specifying connections pool.
     * @param pool specifies connections pool used for communication with db
     * @see org.tof.hotel.jdbc.Pool
     * @see org.tof.hotel.jdbc.JConnection
     */
    public AdministratorDaoImpl(Pool pool) {
        this.pool = pool;
    }

    /**
     * Insert record about Administrator object to the db
     * @param dto data transfer object specifying Administrator parameters
     * @return id of inserted record
     * @throws SQLException if a database access error occurs
     * @see org.tof.hotel.dto.AdministratorDTO
     */
    public int insert(AdministratorDTO dto) throws SQLException {
        JConnection jConn = pool.getConnection();

        PreparedStatement prepStat = jConn.prepareStatement(
                "INSERT INTO administrator " +
                "(firstName, lastName, phone, email, address, points) " +
                "VALUES (?,?,?,?,?,?);");
        prepStat.setString(1,dto.getFirstName().isEmpty() ? "" :  dto.getFirstName());
        prepStat.setString(2,dto.getLastName().isEmpty() ? "" : dto.getLastName());
        prepStat.setString(3,dto.getPhone().isEmpty() ? "" : dto.getPhone());
        prepStat.setString(4,dto.getEmail().isEmpty() ? "" : dto.getEmail());
        prepStat.setString(5,dto.getAddress().isEmpty() ? "" : dto.getAddress());
        prepStat.setInt(6, dto.getPoints() == null ? 0 : dto.getPoints());

        prepStat.executeUpdate();
        pool.releaseConnection(jConn);
        ResultSet rs = prepStat.getGeneratedKeys();
        rs.next();
        return rs.getInt(1);
    }
    /**
     * Returns all Administrator</code> objects from the db which meets the specified criteria
     * @param criteria data transfer object specifying the criteria of selecting records
     * @return id of inserted record
     * @throws SQLException if a database access error occurs
     * @see org.tof.hotel.dto.AdministratorDTO
     */
    public ResultSet select(AdministratorDTO criteria) throws SQLException {
        JConnection jConn = pool.getConnection();

        PreparedStatement prepStat = jConn.prepareStatement(
                        "SELECT * FROM administrator " +
                        "WHERE firstName like ? and lastName like ? and " +
                        "phone like ? and email like ? and address like ? and points like ?");
        prepStat.setString(1,criteria.getFirstName().isEmpty() ? "%" :  criteria.getFirstName());
        prepStat.setString(2,criteria.getLastName().isEmpty() ? "%" : criteria.getLastName());
        prepStat.setString(3,criteria.getPhone().isEmpty() ? "%" : criteria.getPhone());
        prepStat.setString(4,criteria.getEmail().isEmpty() ? "%" : criteria.getEmail());
        prepStat.setString(5,criteria.getAddress().isEmpty() ? "%" : criteria.getAddress());
        prepStat.setString(6, criteria.getPoints() == null ? "%" : Integer.toString(criteria.getPoints()));

        ResultSet rs = prepStat.executeQuery();

        pool.releaseConnection(jConn);

        return rs;
    }

    /**
     * Update the record in db about an <code>Administrator</code>
     * @param dto data transfer object specifying <code>Administrator</code> parameters
     * @return modified rows count
     * @throws SQLException if a database access error occurs
     * @see org.tof.hotel.dto.AdministratorDTO
     */
    public int update(AdministratorDTO dto) throws SQLException {
        JConnection jConn = pool.getConnection();

/*
        PreparedStatement prepStat = jConn.prepareStatement(
                "UPDATE administrator " +
                "SET firstName=?,lastName=?, phone=?, email=?, address=?, points=? " +
                "WHERE id=?;");
        prepStat.setString(1,dto.getFirstName());
        prepStat.setString(2,dto.getLastName());
        prepStat.setString(3,dto.getPhone());
        prepStat.setString(4,dto.getEmail());
        prepStat.setString(5,dto.getAddress());
        prepStat.setInt(6,dto.getPoints());
        prepStat.setInt(7,dto.getId());
*/

        PreparedStatement prepStat = jConn.prepareStatement(
                "UPDATE administrator SET " +
                "firstName=, "  + dto.getFirstName()    +
                "lastName=, "   + dto.getLastName()     +
                "phone=, "      + dto.getPhone()        +
                "email=, "      + dto.getEmail()        +
                "address=, "    + dto.getAddress()      +
                "points=, "     + dto.getPoints()       +
                "WHERE id="     + dto.getId());

        int result = prepStat.executeUpdate();
        pool.releaseConnection(jConn);
        return result;
    }

    /**
     * Deletes the record about specified <code>Administrator</code> from the db
     * @param dto data transfer object specifying an <code>Administrator</code>
     * @return modified rows count
     * @throws SQLException if a database access error occurs
     */
    public int delete(AdministratorDTO dto) throws SQLException {
        JConnection jConn = pool.getConnection();
        PreparedStatement prepStat = jConn.prepareStatement("DELETE from administrator WHERE id=" + dto.getId());
        int result = prepStat.executeUpdate();
        pool.releaseConnection(jConn);
        return result;
    }
}
